Using Rules Manager

Purpose

With this tutorial, you learn how to use Rules Manager feature. This feature is introduced in Oracle Database 10g Release 2.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

· Overview

· Prerequisites

· Modeling a rules application with Rules Manager

· Summary

· Related Information

Overview

Application developers use rules to automatically respond to events generated by processes and workflows. Unfortunately, rules are often embedded in applications or loaded in special purpose memory-based rules repositories making timely rule changes, maintenance and scalability a challenge.

Rules Manager is a feature of Oracle Database 10g Release 2, Standard and Enterprise Editions. It saves money, time and labor by allowing application developers to define rules in Oracle Database that respond to events of any complexity with better scalability and operational characteristics than the alternatives.

· Rules keep pace with changing business conditions more easily in Oracle Database because rule changes are available immediately using SQL INSERT,UPDATE and DELETE statements.

· Rules are stored, indexed and incrementally evaluated efficiently in the database where business context also resides.

· Event data can include XML documents and Oracle Spatial data. Event data can be stored persistently in database tables or received directly from your application.

· Complex event scenarios can be modeled using Rules Manager XML-based rule condition elements.

· Event policies can stipulate event and rule processing behavior.

· Rule actions are flexible; rules can trigger actions by the database or by your application, or both.

Additional advantages of Oracle Database include: scalability, by sharing a single copy of the rules for all applications and evaluating rule sets of any size; manageability, by storing rules and event policies with your application data; and performance, by evaluating rules, and coordinating multiple events and application threads with the full capabilities of Oracle Database.

Applications for Rules Manager include information distribution, task assignment, event-based computing, radio frequency ID (RFID), supply chain, enterprise application integration (EAI), business asset management (BAM), and business process management (BPM).

Prerequisites

Before starting this tutorial, you should have:

1.	Completed the Installation of Oracle Database 10g Release 2 with Sample Schema.
2.	Downloaded and unzipped rulesmanager.zip into your working directory (that is, /home/oracle/wkdir)

Modeling a Rules Application

The Rules Manager can be used in an Order Entry system to track the orders and their shipment in real time and initiate some workflows or raise alerts for some exceptions using the rules defined on the content of the orders.

The application described in this tutorial is an extension of the Order Entry sample schema. In this schema, the items ordered by the customers are captured using an object type order_typ that consists of attributes such as order identifier, customer identifier and mode of the order. A variant of this object type can be used to model one of the event structures used by the rules application. Two other object types, ShipmentInfo, which captures the information about shipment of the ordered items and PaymentInfo, which captures the information about the payment for the ordered items, are used as the other event structures in the rules application.

Perform the following steps to create a rules application for the Order entry system.

1. In a terminal window, change to the /home/oracle/wkdir directory and start SQL*Plus.

Connect to Oracle with user ID oe and password oe.

cd /home/oracle/wkdir
sqlplus oe/oe

2. Create the object types that represent the individual events in the business processes using the following script

@primeventstructs.sql

The eventstructs.sql script contains the following commands:

create type PurchaseOrder as object

(orderId NUMBER,

custId NUMBER,

itemId NUMBER,

itemType VARCHAR2(30),

quantity NUMBER,

shipBy DATE);

create type ShipmentInfo as object

(orderId NUMBER,

destState VARCHAR2(2),

address VARCHAR2(50),

shipTime DATE,

shipType VARCHAR2(10));

create type PaymentInfo as object

(orderId NUMBER,

payType VARCHAR2(10), -- Credit Card / Check --

amountPaid NUMBER,

pymtTime DATE,

billState VARCHAR2(2));

3. Create a composite event structure that represents a combination of all the primitive events in the application. This event structure is created using the following script.

@compeventstruct.sql

The compeventstruct.sql script contains the following commands:

create type OrderMgmt as object
(po      PurchaseOrder,
   si      ShipmentInfo,
   py      PaymentInfo);
/

4. Create the rule class for the OrderMgmt event structure to store and manage rules defined for this application.

@ruleclass.sql

The ruleclass.sql script contains the following commands:

BEGIN
dbms_rlmgr.create_rule_class (
     rule_class      => 'OrderMgmtRC',
     event_struct    => 'OrderMgmt',
     action_cbk      => 'OrderMgmtCBK',
     actprf_spec     => 'actionType VARCHAR2(40), actionParam VARCHAR2(100)',
     rlcls_prop      => '<composite
          equal="po.orderId, si.orderId, py.orderId"
          ordering="rlm$rule.rlm$ruleid, po.orderId"/>’);
END;
/

Note that the above step creates a relational table, OrderMgmtRC, to store the rule definitions and the skeleton for the action callback procedure, OrderMgmtCBK, that acts as the entry point for executing the actions for all the rules in the rule class.

5. View the signature for the action callback procedure created in Step 4. Recreate the procedure with the application logic for rule actions. In this application, the action associated with each rule is to print the information about the rule and the events that matched the rule to the screen as well as insert the information into a messages table, action_msgs. The following script has a sample implementation for the action callback procedure.

@actioncbk.sql

The actioncbk.sql script has the following commands:

describe OrderMgmtCBK;

create table action_msgs (attime date, mesg varchar2(1000));

CREATE OR REPLACE PROCEDURE OrderMgmtCBK (

po PurchaseOrder,

si ShipmentInfo,

py PaymentInfo,

rlm$rule OrderMgmtRC%ROWTYPE) IS

msg VARCHAR2(2000);

begin

msg := 'Order number: '||po.orderId||' Matched rule: '

||rlm$rule.rlm$ruleid||chr(10)||

'-> Recommended Action : '||chr(10)||

' Action Type ['||rlm$rule.actionType||

']'||chr(10)||' Action Parameter ['||

rlm$rule.actionParam||']';

dbms_output.put_line (msg||chr(10));

insert into action_msgs values (sysdate, msg);

end;

6. Create user-defined functions that may be used in forming some predicates in the rule conditions and add them to the event structure. The following script creates a function to obtain the credit limit of a given customer. The function is implemented to fetch the credit limit of a customer from the Customers table in OE schema.

@userdeffunc.sql

The userdeffunc.sql script has the following commands to create the function and then add the function to the list of approved functions for the rules application.

CREATE FUNCTION getCustCredit(custId number) return number is

retval NUMBER;

begin

select credit_limit into retval from oe.Customers where customer_id = custId;

return retval;

exception

when no_data_found then

return 0;

end;

EXEC dbms_rlmgr.add_functions ('OrderMgmt','getCustCredit');

7. Add one or more rules to the rule class. The following script has some sample rules that are typical of an Order Entry system. The description for each rule is included in the rule definition.

@addrules.sql

The addrules.sql script has the following SQL commands to insert three rules into the rule class.

INSERT INTO OrderMgmtRC (rlm$ruleid, rlm$ruledesc, rlm$rulecond,

actionType, actionParam) VALUES

(‘CHECK_PAYMENT_UPDATE’,

‘If the order is for more than 100 routers and the payment is received

as a check, contact the customer to update on the status of the order.’,

'<condition>

itemType = ''ROUTER'' and quantity > 100

</object>

payType = ''CHECK''

</object>

</and>

</condition>',

'CALL_CUSTOMER','UPDATE_ORDER_STATUS');

INSERT INTO OrderMgmtRC (rlm$ruleid, rlm$ruledesc, rlm$rulecond,

actionType, actionParam) VALUES

(‘ADJUST_CUSTOMER_CREDIT’,

‘If the order is placed by a customer with over 100000 credit limit and

the items are shipped before receiving a payment, adjust the customer'’s

credit.’,

‘<condition>

<not>

</not>

</and>

</condition>',

'UPDATE_CUST_PROFILE', 'DECR_AVAILABLE_CREDIT');

INSERT INTO OrderMgmtRC (rlm$ruleid, rlm$ruledesc, rlm$rulecond,

actionType, actionParam) VALUES

(‘DELAY_SHIPMENT_GOLD_CUSTOMER_’,

‘If the order is placed by a customer with large credit and the item

is not shipped by 1 day prior to the shipDate specified in the order,

alert a representative’,

‘<condition>

</not>

</and>

</condition>',

'ALERT_AGENT', 'DELAYED SHIPMENT');

8. Process the rules for a set of events and want the output from matching the events with the above rules.

@processrules.sql

The processrules.sql script has the following commands:

set serveroutput on;

begin

dbms_rlmgr.process_rules(

rule_class =>’OrderMgmtRC’,

event_inst =>PurchaseOrder(1,123,234, ‘ROUTER’,120,

’01-MAY-2005’).getVarchar(),

event_type =>’PurchaseOrder’);

end;

begin

dbms_rlmgr.process_rules(

rule_class =>’OrderMgmtRC’,

event_inst =>ShipmentInfo(1, 'CA','1 Main street, San Jose',

'29-SEP-2004','1 Day Air').getVarchar(),

event_type =>’ShipmentInfo’);

end;

begin

dbms_rlmgr.process_rules(

rule_class =>’OrderMgmtRC’,

event_inst =>PaymentInfo(1, 'CHECK', 100000, '30-SEP-2004',’CA’).getVarchar(),

event_type =>’PaymentInfo’);

end;

select * from action_msgs;

While processing the rules in a rule class for a set of events, each rule acts as a state machine, which reaches an accepting state when all the necessary events are processed. At this time, the action callback procedure is invoked by passing in the information about the rule as well as the events that matched the rule. The action callback procedure can be implemented to make use of other database technologies such as message queues and scheduler to perform the prescribed action.

Summary

In this tutorial, you've learned how to create a rules application involving composite events and create rules using XML and SQL based rule condition language.

Using Rules Manager

Purpose

Time to Complete

Topics

Overview

Prerequisites

Modeling a Rules Application

2. Create the object types that represent the individual events in the business processes using the following script @primeventstructs.sql

3. Create a composite event structure that represents a combination of all the primitive events in the application. This event structure is created using the following script.

@compeventstruct.sql

The compeventstruct.sql script contains the following commands:

create type OrderMgmt as object (po PurchaseOrder, si ShipmentInfo, py PaymentInfo); /

4. Create the rule class for the OrderMgmt event structure to store and manage rules defined for this application. @ruleclass.sql

The ruleclass.sql script contains the following commands:

Note that the above step creates a relational table, OrderMgmtRC, to store the rule definitions and the skeleton for the action callback procedure, OrderMgmtCBK, that acts as the entry point for executing the actions for all the rules in the rule class.

@userdeffunc.sql

2. Create the object types that represent the individual events in the business processes using the following script

@primeventstructs.sql

create type OrderMgmt as object
(po PurchaseOrder,
si ShipmentInfo,
py PaymentInfo);
/

4. Create the rule class for the OrderMgmt event structure to store and manage rules defined for this application.

@ruleclass.sql